using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.Common.BO;
using PCSComUtils.DataAccess;

using PCSComUtils.PCSExc;
using PCSComUtils.DataContext;
using System.Text;

namespace PCSComProduction.WorkOrder.DS
{
    public class PRO_WorkOrderCompletionDS
    {
        const string PRO = " Product";
        const string UM = "UnitOfMeasure";
        const string CAPTION_UM = "UM";
        const string WOD = " WODetail";
        const string CQ = " Compqty";
        const string SQ = " Scrapqty";
        const string OPENQUANTITY = "OpenQuantity";
        const string COMPQTY = "CompletedQuantity";
        const string SCRAPQUANTITY = "ScrapQuantity";
        const string WOC = " WOCompletion";
        const string WOM = " WOMaster";
        const string LOC = " Location";
        const string BIN = " Bin";

        public PRO_WorkOrderCompletionDS()
        {
        }
        private const string THIS = "PCSComProduction.WorkOrder.DS.PRO_WorkOrderCompletionDS";
        //**************************************************************************              
        ///    <Description>
        ///       This method uses to add data to PRO_WorkOrderCompletion
        ///    </Description>
        ///    <Inputs>
        ///        PRO_WorkOrderCompletionVO       
        ///    </Inputs>
        ///    <Outputs>
        ///       newly inserted primarkey value
        ///    </Outputs>
        ///    <Returns>
        ///       void
        ///    </Returns>
        ///    <Authors>
        ///       HungLa
        ///    </Authors>
        ///    <History>
        ///       Tuesday, May 31, 2005
        ///    </History>
        ///    <Notes>
        ///    </Notes>
        //**************************************************************************

        public DataTable GetAvailableQuantityByProductionLinePostDate(DateTime pdtmPostDate, int pintProductionLineID, int pintWODetailId)
        {
            const string BINAVAILABLE_FLD = "BinAvailable";

            DataTable dtbResultTable = new DataTable();

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;

            if (pdtmPostDate == DateTime.MinValue)
            {
                pdtmPostDate = (new UtilsBO()).GetDBDate();
            }

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT DISTINCT");
            strSql.Append(" child.ProductID,");
            strSql.Append(" (ISNULL(SUM(ISNULL(bc.OHQuantity, 0) - ISNULL(bc.CommitQuantity, 0)), 0)");
            strSql.Append(" ) as " + BINAVAILABLE_FLD);
            strSql.Append(" , bc.ProductID");
            strSql.Append(" , bc.BinID");
            strSql.Append(" , bc.LocationID");
            strSql.Append(" FROM PRO_WorkOrderDetail woi");
            strSql.Append(" INNER JOIN PRO_WOBOM bom ON woi.ProductID = bom.ProductID");
            strSql.Append(" INNER JOIN ITM_Product child ON bom.ComponentID = child.ProductID");
            strSql.Append(" INNER JOIN IV_BinCache bc ON bc.ProductID = child.ProductID");
            strSql.Append(" INNER JOIN MST_BIN ON MST_BIN.BinID = bc.BinID");
            strSql.Append(" INNER JOIN PRO_ProductionLine ON PRO_ProductionLine.LocationID = bc.LocationID");

            strSql.Append(" WHERE MST_BIN.BinTypeID = " + (int)BinTypeEnum.IN);
            strSql.Append(" AND PRO_ProductionLine.ProductionLineID = " + pintProductionLineID);
            strSql.Append(" AND woi.WorkOrderDetailID = " + pintWODetailId);

            strSql.Append(" GROUP BY bc.ProductID,  bc.BinID,  bc.LocationID,");
            strSql.Append(" bc.CCNID,  bc.MasterLocationID, child.ProductID");

            oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
            ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);
            //			ocmdPCS.CommandTimeout = 10000;

            ocmdPCS.Connection.Open();
            OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
            odadPCS.Fill(dtbResultTable);

            return dtbResultTable;
        }

        public void Add(object pobjObjectVO)
        {
            const string METHOD_NAME = THIS + ".Add()";

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                PRO_WorkOrderCompletionVO objObject = (PRO_WorkOrderCompletionVO)pobjObjectVO;
                string strSql = String.Empty;
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand("", oconPCS);

                strSql = "INSERT INTO PRO_WorkOrderCompletion("
                    + PRO_WorkOrderCompletionTable.POSTDATE_FLD + ","
                    + PRO_WorkOrderCompletionTable.WOCOMPLETIONNO_FLD + ","
                    + PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD + ","
                    + PRO_WorkOrderCompletionTable.LOT_FLD + ","
                    + PRO_WorkOrderCompletionTable.SERIAL_FLD + ","
                    + PRO_WorkOrderCompletionTable.LOCATIONID_FLD + ","
                    + PRO_WorkOrderCompletionTable.BINID_FLD + ","
                    + PRO_WorkOrderCompletionTable.CCNID_FLD + ","
                    + PRO_WorkOrderCompletionTable.MASTERLOCATIONID_FLD + ","
                    + PRO_WorkOrderCompletionTable.PRODUCTID_FLD + ","
                    + PRO_WorkOrderCompletionTable.STOCKUMID_FLD + ","
                    + PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD + ","
                    + PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD + ","
                    + PRO_WorkOrderCompletionTable.SHIFTID_FLD + ","
                    + PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD + ","
                    + PRO_WorkOrderCompletionTable.REMARK_FLD + ","
                    + PRO_WorkOrderCompletionTable.QASTATUS_FLD + ")"
                    + "VALUES(?, ?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.POSTDATE_FLD, OleDbType.Date));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.POSTDATE_FLD].Value = objObject.PostDate;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.WOCOMPLETIONNO_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.WOCOMPLETIONNO_FLD].Value = objObject.WOCompletionNo;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD].Value = objObject.CompletedQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.LOT_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.LOT_FLD].Value = objObject.Lot;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.SERIAL_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.SERIAL_FLD].Value = objObject.Serial;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.LOCATIONID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.LOCATIONID_FLD].Value = objObject.LocationID;


                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.BINID_FLD, OleDbType.Integer));
                if (objObject.BinID != 0)
                {
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.BINID_FLD].Value = objObject.BinID;
                }
                else
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.BINID_FLD].Value = DBNull.Value;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.CCNID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.CCNID_FLD].Value = objObject.CCNID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.MASTERLOCATIONID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.MASTERLOCATIONID_FLD].Value = objObject.MasterLocationID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.PRODUCTID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.PRODUCTID_FLD].Value = objObject.ProductID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.STOCKUMID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.STOCKUMID_FLD].Value = objObject.StockUMID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD].Value = objObject.WorkOrderMasterID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD].Value = objObject.WorkOrderDetailID;

                //HACKED: Added by Tuan TQ. 09 Dec, 2005: Add more properties
                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.SHIFTID_FLD, OleDbType.Integer));
                if (objObject.ShiftID > 0)
                {
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.SHIFTID_FLD].Value = objObject.ShiftID;
                }
                else
                {
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.SHIFTID_FLD].Value = DBNull.Value;
                }

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD, OleDbType.Integer));
                if (objObject.IssuePurposeID > 0)
                {
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD].Value = objObject.IssuePurposeID;
                }
                else
                {
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD].Value = DBNull.Value;
                }

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.REMARK_FLD, OleDbType.WChar));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.REMARK_FLD].Value = objObject.Remark;

                //End Hacked
                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.QASTATUS_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.QASTATUS_FLD].Value = objObject.QAStatus;



                ocmdPCS.CommandText = strSql;
                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();

            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                {

                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }
        /// <summary>
        /// AddAndReturnID
        /// </summary>
        /// <param name="pobjObjectVO"></param>
        /// <returns></returns>
        /// <author>Trada</author>
        /// <date>Friday, August 19 2005</date>
        public int AddAndReturnID(object pobjObjectVO)
        {
            const string METHOD_NAME = THIS + ".AddAndReturnID()";

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                PRO_WorkOrderCompletionVO objObject = (PRO_WorkOrderCompletionVO)pobjObjectVO;
                string strSql = String.Empty;
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand("", oconPCS);
                //				ocmdPCS.CommandTimeout = 10000;

                strSql = "INSERT INTO PRO_WorkOrderCompletion("
                    + PRO_WorkOrderCompletionTable.POSTDATE_FLD + ","
                    + PRO_WorkOrderCompletionTable.WOCOMPLETIONDAT_FLD + ","
                    + PRO_WorkOrderCompletionTable.WOCOMPLETIONNO_FLD + ","
                    + PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD + ","
                    + PRO_WorkOrderCompletionTable.LOT_FLD + ","
                    + PRO_WorkOrderCompletionTable.SERIAL_FLD + ","
                    + PRO_WorkOrderCompletionTable.LOCATIONID_FLD + ","
                    + PRO_WorkOrderCompletionTable.BINID_FLD + ","
                    + PRO_WorkOrderCompletionTable.CCNID_FLD + ","
                    + PRO_WorkOrderCompletionTable.MASTERLOCATIONID_FLD + ","
                    + PRO_WorkOrderCompletionTable.PRODUCTID_FLD + ","
                    + PRO_WorkOrderCompletionTable.STOCKUMID_FLD + ","
                    + PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD + ","
                    + PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD + ","
                    + PRO_WorkOrderCompletionTable.SHIFTID_FLD + ","
                    + PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD + ","
                    + PRO_WorkOrderCompletionTable.REMARK_FLD + ","
                    + PRO_WorkOrderCompletionTable.USERNAME_FLD + ","
                    + PRO_WorkOrderCompletionTable.QASTATUS_FLD + ")"
                    + "VALUES(?, ?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) SELECT @@IDENTITY";

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.POSTDATE_FLD, OleDbType.Date));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.POSTDATE_FLD].Value = objObject.PostDate;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.WOCOMPLETIONDAT_FLD, OleDbType.Date));
                if (objObject.WOCompletionDate != DateTime.MinValue)
                {
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.WOCOMPLETIONDAT_FLD].Value = objObject.WOCompletionDate;
                }
                else
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.WOCOMPLETIONDAT_FLD].Value = DBNull.Value;
                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.WOCOMPLETIONNO_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.WOCOMPLETIONNO_FLD].Value = objObject.WOCompletionNo;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD].Value = objObject.CompletedQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.LOT_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.LOT_FLD].Value = objObject.Lot;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.SERIAL_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.SERIAL_FLD].Value = objObject.Serial;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.LOCATIONID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.LOCATIONID_FLD].Value = objObject.LocationID;


                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.BINID_FLD, OleDbType.Integer));
                if (objObject.BinID != 0)
                {
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.BINID_FLD].Value = objObject.BinID;
                }
                else
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.BINID_FLD].Value = DBNull.Value;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.CCNID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.CCNID_FLD].Value = objObject.CCNID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.MASTERLOCATIONID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.MASTERLOCATIONID_FLD].Value = objObject.MasterLocationID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.PRODUCTID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.PRODUCTID_FLD].Value = objObject.ProductID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.STOCKUMID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.STOCKUMID_FLD].Value = objObject.StockUMID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD].Value = objObject.WorkOrderMasterID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD].Value = objObject.WorkOrderDetailID;

                //HACKED: Added by Tuan TQ. 09 Dec, 2005: Add more properties
                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.SHIFTID_FLD, OleDbType.Integer));
                if (objObject.ShiftID > 0)
                {
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.SHIFTID_FLD].Value = objObject.ShiftID;
                }
                else
                {
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.SHIFTID_FLD].Value = DBNull.Value;
                }

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD, OleDbType.Integer));
                if (objObject.IssuePurposeID > 0)
                {
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD].Value = objObject.IssuePurposeID;
                }
                else
                {
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD].Value = DBNull.Value;
                }

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.REMARK_FLD, OleDbType.WChar));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.REMARK_FLD].Value = objObject.Remark;
                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.USERNAME_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.USERNAME_FLD].Value = objObject.Username;

                //End Hacked

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.QASTATUS_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.QASTATUS_FLD].Value = objObject.QAStatus;

                ocmdPCS.CommandText = strSql;
                ocmdPCS.Connection.Open();
                object objResult = ocmdPCS.ExecuteScalar();
                if ((objResult != null) && (objResult != DBNull.Value))
                {
                    return int.Parse(objResult.ToString());
                }

            }
            catch (OleDbException ex)
            {
                if (ex.Errors.Count > 1)
                {
                    if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                        throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                    else
                        throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
                else
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
            return 0;
        }
        //**************************************************************************              
        ///    <Description>
        ///       This method uses to delete data from PRO_WorkOrderCompletion
        ///    </Description>
        ///    <Inputs>
        ///        ID       
        ///    </Inputs>
        ///    <Outputs>
        ///       void
        ///    </Outputs>
        ///    <Returns>
        ///       
        ///    </Returns>
        ///    <Authors>
        ///       HungLa
        ///    </Authors>
        ///    <History>
        ///       09-Dec-2004
        ///    </History>
        ///    <Notes>
        ///    </Notes>
        //**************************************************************************

        public void Delete(int pintID)
        {
            const string METHOD_NAME = THIS + ".Delete()";
            string strSql = String.Empty;
            strSql = "DELETE " + PRO_WorkOrderCompletionTable.TABLE_NAME + " WHERE  " + "WorkOrderCompletionID" + "=" + pintID.ToString();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);

                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();
                ocmdPCS = null;

            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
                {

                    throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }



            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        //**************************************************************************              
        ///    <Description>
        ///       This method uses to get data from PRO_WorkOrderCompletion
        ///    </Description>
        ///    <Inputs>
        ///        ID       
        ///    </Inputs>
        ///    <Outputs>
        ///       PRO_WorkOrderCompletionVO
        ///    </Outputs>
        ///    <Returns>
        ///       PRO_WorkOrderCompletionVO
        ///    </Returns>
        ///    <Authors>
        ///       HungLa
        ///    </Authors>
        ///    <History>
        ///       Tuesday, May 31, 2005
        ///    </History>
        ///    <Notes>
        ///    </Notes>
        //**************************************************************************

        public object GetObjectVO(int pintID)
        {
            const string METHOD_NAME = THIS + ".GetObjectVO()";
            DataSet dstPCS = new DataSet();

            OleDbDataReader odrPCS = null;
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;
                strSql = "SELECT "
                    + PRO_WorkOrderCompletionTable.WORKORDERCOMPLETIONID_FLD + ","
                    + PRO_WorkOrderCompletionTable.POSTDATE_FLD + ","
                    + PRO_WorkOrderCompletionTable.WOCOMPLETIONNO_FLD + ","
                    + PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD + ","
                    + PRO_WorkOrderCompletionTable.NGQUANTITY_FLD + ","
                    + PRO_WorkOrderCompletionTable.LOT_FLD + ","
                    + PRO_WorkOrderCompletionTable.SERIAL_FLD + ","
                    + PRO_WorkOrderCompletionTable.LOCATIONID_FLD + ","
                    + PRO_WorkOrderCompletionTable.BINID_FLD + ","
                    + PRO_WorkOrderCompletionTable.CCNID_FLD + ","
                    + PRO_WorkOrderCompletionTable.MASTERLOCATIONID_FLD + ","
                    + PRO_WorkOrderCompletionTable.PRODUCTID_FLD + ","
                    + PRO_WorkOrderCompletionTable.STOCKUMID_FLD + ","
                    + PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD + ","
                    + PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD + ","
                    + PRO_WorkOrderCompletionTable.SHIFTID_FLD + ","
                    + PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD + ","
                    + PRO_WorkOrderCompletionTable.REMARK_FLD + ","
                    + PRO_WorkOrderCompletionTable.QASTATUS_FLD
                    + " FROM " + PRO_WorkOrderCompletionTable.TABLE_NAME
                    + " WHERE " + PRO_WorkOrderCompletionTable.WORKORDERCOMPLETIONID_FLD + "=" + pintID;

                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);

                ocmdPCS.Connection.Open();
                odrPCS = ocmdPCS.ExecuteReader();

                PRO_WorkOrderCompletionVO objObject = new PRO_WorkOrderCompletionVO();

                while (odrPCS.Read())
                {
                    objObject.WorkOrderCompletionID = int.Parse(odrPCS[PRO_WorkOrderCompletionTable.WORKORDERCOMPLETIONID_FLD].ToString().Trim());
                    objObject.PostDate = DateTime.Parse(odrPCS[PRO_WorkOrderCompletionTable.POSTDATE_FLD].ToString().Trim());
                    objObject.WOCompletionNo = odrPCS[PRO_WorkOrderCompletionTable.WOCOMPLETIONNO_FLD].ToString().Trim();
                    objObject.CompletedQuantity = Decimal.Parse(odrPCS[PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD].ToString().Trim());
                    try
                    {
                        objObject.NGQuantity = Decimal.Parse(odrPCS[PRO_WorkOrderCompletionTable.NGQUANTITY_FLD].ToString().Trim());
                    }
                    catch { }
                    objObject.Lot = odrPCS[PRO_WorkOrderCompletionTable.LOT_FLD].ToString().Trim();
                    objObject.Serial = odrPCS[PRO_WorkOrderCompletionTable.SERIAL_FLD].ToString().Trim();
                    objObject.LocationID = int.Parse(odrPCS[PRO_WorkOrderCompletionTable.LOCATIONID_FLD].ToString().Trim());
                    objObject.CCNID = int.Parse(odrPCS[PRO_WorkOrderCompletionTable.CCNID_FLD].ToString().Trim());
                    objObject.MasterLocationID = int.Parse(odrPCS[PRO_WorkOrderCompletionTable.MASTERLOCATIONID_FLD].ToString().Trim());
                    objObject.ProductID = int.Parse(odrPCS[PRO_WorkOrderCompletionTable.PRODUCTID_FLD].ToString().Trim());
                    objObject.StockUMID = int.Parse(odrPCS[PRO_WorkOrderCompletionTable.STOCKUMID_FLD].ToString().Trim());
                    objObject.WorkOrderMasterID = int.Parse(odrPCS[PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD].ToString().Trim());
                    objObject.WorkOrderDetailID = int.Parse(odrPCS[PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD].ToString().Trim());

                    //HACKED: edit by Tuan TQ, 09 Dec, 2005: Valid data before parse
                    if (!odrPCS[PRO_WorkOrderCompletionTable.BINID_FLD].Equals(DBNull.Value))
                    {
                        objObject.BinID = int.Parse(odrPCS[PRO_WorkOrderCompletionTable.BINID_FLD].ToString().Trim());
                    }
                    if (!odrPCS[PRO_WorkOrderCompletionTable.QASTATUS_FLD].Equals(DBNull.Value))
                    {
                        objObject.QAStatus = int.Parse(odrPCS[PRO_WorkOrderCompletionTable.QASTATUS_FLD].ToString().Trim());
                    }
                    //HACKED: Added by Tuan TQ. 09 Dec, 2005. Add more properties
                    if (!odrPCS[PRO_WorkOrderCompletionTable.SHIFTID_FLD].Equals(DBNull.Value))
                    {
                        objObject.ShiftID = int.Parse(odrPCS[PRO_WorkOrderCompletionTable.SHIFTID_FLD].ToString().Trim());
                    }

                    if (!odrPCS[PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD].Equals(DBNull.Value))
                    {
                        objObject.IssuePurposeID = int.Parse(odrPCS[PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD].ToString().Trim());
                    }

                    objObject.Remark = odrPCS[PRO_WorkOrderCompletionTable.REMARK_FLD].ToString().Trim();

                    //End hacked

                }
                return objObject;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }

            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }

        public void Update(PRO_WorkOrderCompletion objObject)
        {
            const string METHOD_NAME = THIS + ".Update()";

            //prepare value for parameters
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                strSql = "UPDATE PRO_WorkOrderCompletion SET "
                    + PRO_WorkOrderCompletionTable.POSTDATE_FLD + "=   ?" + ","
                    + PRO_WorkOrderCompletionTable.WOCOMPLETIONNO_FLD + "=   ?" + ","
                    + PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD + "=   ?" + ","
                    + PRO_WorkOrderCompletionTable.LOT_FLD + "=   ?" + ","
                    + PRO_WorkOrderCompletionTable.SERIAL_FLD + "=   ?" + ","
                    + PRO_WorkOrderCompletionTable.LOCATIONID_FLD + "=   ?" + ","
                    + PRO_WorkOrderCompletionTable.BINID_FLD + "=   ?" + ","
                    + PRO_WorkOrderCompletionTable.CCNID_FLD + "=   ?" + ","
                    + PRO_WorkOrderCompletionTable.MASTERLOCATIONID_FLD + "=   ?" + ","
                    + PRO_WorkOrderCompletionTable.PRODUCTID_FLD + "=   ?" + ","
                    + PRO_WorkOrderCompletionTable.STOCKUMID_FLD + "=   ?" + ","
                    + PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD + "=   ?" + ","
                    + PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD + "=   ?" + ","
                    + PRO_WorkOrderCompletionTable.SHIFTID_FLD + " = ?, "
                    + PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD + " =?, "
                    + PRO_WorkOrderCompletionTable.REMARK_FLD + " = ?, "
                    + PRO_WorkOrderCompletionTable.NGQUANTITY_FLD + " = ?, "
                    + PRO_WorkOrderCompletionTable.KCSUSER_FLD + " = ?, "
                    + PRO_WorkOrderCompletionTable.QASTATUS_FLD + "=  ?"
                    + " WHERE " + PRO_WorkOrderCompletionTable.WORKORDERCOMPLETIONID_FLD + "= ?";

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.POSTDATE_FLD, OleDbType.Date));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.POSTDATE_FLD].Value = objObject.PostDate;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.WOCOMPLETIONNO_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.WOCOMPLETIONNO_FLD].Value = objObject.WOCompletionNo;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD].Value = objObject.CompletedQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.LOT_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.LOT_FLD].Value = objObject.Lot;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.SERIAL_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.SERIAL_FLD].Value = objObject.Serial;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.LOCATIONID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.LOCATIONID_FLD].Value = objObject.LocationID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.BINID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.BINID_FLD].Value = objObject.BinID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.CCNID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.CCNID_FLD].Value = objObject.CCNID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.MASTERLOCATIONID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.MASTERLOCATIONID_FLD].Value = objObject.MasterLocationID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.PRODUCTID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.PRODUCTID_FLD].Value = objObject.ProductID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.STOCKUMID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.STOCKUMID_FLD].Value = objObject.StockUMID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD].Value = objObject.WorkOrderMasterID;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD].Value = objObject.WorkOrderDetailID;

                //HACKED: Added by Tuan TQ. 09 Dec, 2005: Add more properties
                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.SHIFTID_FLD, OleDbType.Integer));
                if (objObject.ShiftID > 0)
                {
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.SHIFTID_FLD].Value = objObject.ShiftID;
                }
                else
                {
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.SHIFTID_FLD].Value = DBNull.Value;
                }

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD, OleDbType.Integer));
                if (objObject.IssuePurposeID > 0)
                {
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD].Value = objObject.IssuePurposeID;
                }
                else
                {
                    ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD].Value = DBNull.Value;
                }

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.REMARK_FLD, OleDbType.WChar));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.REMARK_FLD].Value = objObject.Remark;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.NGQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.NGQUANTITY_FLD].Value = objObject.NGQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.KCSUSER_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.KCSUSER_FLD].Value = objObject.KCSUser;

                //End Hacked

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.QASTATUS_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.QASTATUS_FLD].Value = objObject.QAStatus;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.WORKORDERCOMPLETIONID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.WORKORDERCOMPLETIONID_FLD].Value = objObject.WorkOrderCompletionID;


                ocmdPCS.CommandText = strSql;
                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                {

                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }

        }
        public void UpdateInfo(int pintCompletionID, decimal pdecNGQuantity, int pintQAStatus, string strKCSUser)
        {
            const string METHOD_NAME = THIS + ".Update()";

            //prepare value for parameters
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = "UPDATE PRO_WorkOrderCompletion SET "
                    + PRO_WorkOrderCompletionTable.NGQUANTITY_FLD + " = ?, "
                    + PRO_WorkOrderCompletionTable.KCSUSER_FLD + " = ?, "
                    + PRO_WorkOrderCompletionTable.QASTATUS_FLD + "=  ?"
                    + " WHERE " + PRO_WorkOrderCompletionTable.WORKORDERCOMPLETIONID_FLD + "= ?";
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.NGQUANTITY_FLD, OleDbType.Decimal));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.NGQUANTITY_FLD].Value = pdecNGQuantity;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.KCSUSER_FLD, OleDbType.VarWChar));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.KCSUSER_FLD].Value = strKCSUser;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.QASTATUS_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.QASTATUS_FLD].Value = pintQAStatus;

                ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderCompletionTable.WORKORDERCOMPLETIONID_FLD, OleDbType.Integer));
                ocmdPCS.Parameters[PRO_WorkOrderCompletionTable.WORKORDERCOMPLETIONID_FLD].Value = pintCompletionID;

                ocmdPCS.CommandText = strSql;
                //				ocmdPCS.CommandTimeout = 10000;
                ocmdPCS.Connection.Open();
                ocmdPCS.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                throw ex;
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }

        }

        //**************************************************************************              
        ///    <Description>
        ///       This method uses to get all data from PRO_WorkOrderCompletion
        ///    </Description>
        ///    <Inputs>
        ///               
        ///    </Inputs>
        ///    <Outputs>
        ///       DataSet
        ///    </Outputs>
        ///    <Returns>
        ///       DataSet
        ///    </Returns>
        ///    <Authors>
        ///       HungLa
        ///    </Authors>
        ///    <History>
        ///       Tuesday, May 31, 2005
        ///    </History>
        ///    <Notes>
        ///    </Notes>
        //**************************************************************************
        public DataSet List()
        {
            const string METHOD_NAME = THIS + ".List()";
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = "SELECT "
                    + PRO_WorkOrderCompletionTable.WORKORDERCOMPLETIONID_FLD + ","
                    + PRO_WorkOrderCompletionTable.POSTDATE_FLD + ","
                    + PRO_WorkOrderCompletionTable.WOCOMPLETIONNO_FLD + ","
                    + PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD + ","
                    + PRO_WorkOrderCompletionTable.LOT_FLD + ","
                    + PRO_WorkOrderCompletionTable.SERIAL_FLD + ","
                    + PRO_WorkOrderCompletionTable.LOCATIONID_FLD + ","
                    + PRO_WorkOrderCompletionTable.BINID_FLD + ","
                    + PRO_WorkOrderCompletionTable.CCNID_FLD + ","
                    + PRO_WorkOrderCompletionTable.MASTERLOCATIONID_FLD + ","
                    + PRO_WorkOrderCompletionTable.PRODUCTID_FLD + ","
                    + PRO_WorkOrderCompletionTable.STOCKUMID_FLD + ","
                    + PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD + ","
                    + PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD + ","
                    + PRO_WorkOrderCompletionTable.SHIFTID_FLD + ","
                    + PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD + ","
                    + PRO_WorkOrderCompletionTable.REMARK_FLD + ","
                    + PRO_WorkOrderCompletionTable.QASTATUS_FLD
                    + " FROM " + PRO_WorkOrderCompletionTable.TABLE_NAME;
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, PRO_WorkOrderCompletionTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }


        }

        //**************************************************************************              
        ///    <Description>
        ///       This method uses to update a DataSet
        ///    </Description>
        ///    <Inputs>
        ///        DataSet       
        ///    </Inputs>
        ///    <Outputs>
        ///       
        ///    </Outputs>
        ///    <Returns>
        ///       
        ///    </Returns>
        ///    <Authors>
        ///       HungLa
        ///    </Authors>
        ///    <History>
        ///       Tuesday, May 31, 2005
        ///    </History>
        ///    <Notes>
        ///    </Notes>
        //**************************************************************************

        public void UpdateDataSet(DataSet pData)
        {
            const string METHOD_NAME = THIS + ".UpdateDataSet()";
            string strSql;
            OleDbConnection oconPCS = null;
            OleDbCommandBuilder odcbPCS;
            OleDbDataAdapter odadPCS = new OleDbDataAdapter();

            try
            {
                strSql = "SELECT "
                    + PRO_WorkOrderCompletionTable.WORKORDERCOMPLETIONID_FLD + ","
                    + PRO_WorkOrderCompletionTable.POSTDATE_FLD + ","
                    + PRO_WorkOrderCompletionTable.WOCOMPLETIONNO_FLD + ","
                    + PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD + ","
                    + PRO_WorkOrderCompletionTable.LOT_FLD + ","
                    + PRO_WorkOrderCompletionTable.SERIAL_FLD + ","
                    + PRO_WorkOrderCompletionTable.LOCATIONID_FLD + ","
                    + PRO_WorkOrderCompletionTable.BINID_FLD + ","
                    + PRO_WorkOrderCompletionTable.CCNID_FLD + ","
                    + PRO_WorkOrderCompletionTable.MASTERLOCATIONID_FLD + ","
                    + PRO_WorkOrderCompletionTable.PRODUCTID_FLD + ","
                    + PRO_WorkOrderCompletionTable.STOCKUMID_FLD + ","
                    + PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD + ","
                    + PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD + ","
                    + PRO_WorkOrderCompletionTable.SHIFTID_FLD + ","
                    + PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD + ","
                    + PRO_WorkOrderCompletionTable.REMARK_FLD + ","
                    + PRO_WorkOrderCompletionTable.QASTATUS_FLD
                    + "  FROM " + PRO_WorkOrderCompletionTable.TABLE_NAME;

                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
                odcbPCS = new OleDbCommandBuilder(odadPCS);
                pData.EnforceConstraints = false;
                odadPCS.Update(pData, PRO_WorkOrderCompletionTable.TABLE_NAME);

            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                {

                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                }
                else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
                {

                    throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
                }

                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }

            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }

        }
        /// <summary>
        /// get all completed quantity of a WorkOrderDetail = 
        /// SELECT ISNULL(SUM(CompletedQuantity),0) 
        /// FROM PRO_WorkOrderCompletion 
        /// WHERE WorkOrderMasterID = pintWorkOrderMasterID 
        /// 	AND WorkOrderDetailID = pintWorkOrderDetailID
        /// </summary>
        /// <returns>CompletedQuantity</returns>
        public decimal GetCompletedQuantity(int pintWorkOrderMasterID, int pintWorkOrderDetailID)
        {
            const string METHOD_NAME = THIS + ".GetCompletedQuantity()";

            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);

                string strSql = "SELECT ISNULL(SUM(" + PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD + "), 0)";
                strSql += " FROM " + PRO_WorkOrderCompletionTable.TABLE_NAME;
                strSql += " WHERE " + PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD + "=" + pintWorkOrderMasterID;
                strSql += " AND " + PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD + "=" + pintWorkOrderDetailID;

                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                object objResult = ocmdPCS.ExecuteScalar();

                if ((objResult != null) && (objResult != DBNull.Value))
                {
                    return decimal.Parse(objResult.ToString());
                }

                return 0;
            }
            catch (OleDbException ex)
            {
                if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
                {

                    throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
                }
                else
                {
                    throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
                }
            }
            catch (InvalidOperationException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }
            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }

        }
        /// <summary>
        /// Get WO completion detail to load on form 
        /// </summary>
        /// <param name="pintWOCompletionID"></param>
        /// <returns></returns>
        public DataRow GetWOCompletion(int pintWOCompletionID)
        {
            const string LOCATION_CODE = "LocationCode";
            const string BIN_CODE = "BinCode";
            const string METHOD_NAME = THIS + ".GetWOCompletion()";
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = String.Empty;

                strSql = "SELECT "
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.WORKORDERCOMPLETIONID_FLD + ","
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.POSTDATE_FLD + ","
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.WOCOMPLETIONDAT_FLD + ","
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.WOCOMPLETIONNO_FLD + ","
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD + ","
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.NGQUANTITY_FLD + ","
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.LOT_FLD + ","
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.SERIAL_FLD + ","
                    + LOC + Constants.DOT + MST_LocationTable.CODE_FLD + Constants.WHITE_SPACE + LOCATION_CODE + ","
                    + BIN + Constants.DOT + MST_BINTable.CODE_FLD + Constants.WHITE_SPACE + BIN_CODE + ","
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.CCNID_FLD + ","
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.MASTERLOCATIONID_FLD + ","
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.PRODUCTID_FLD + ","
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.STOCKUMID_FLD + ","
                    + WOM + Constants.DOT + PRO_WorkOrderMasterTable.WORKORDERNO_FLD + ","
                    + WOM + Constants.DOT + PRO_WorkOrderMasterTable.WORKORDERMASTERID_FLD + ","
                    + WOD + Constants.DOT + PRO_WorkOrderDetailTable.LINE_FLD + ","
                    // added: dungla. get order quantity
                    + WOD + Constants.DOT + PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD + ","

                    //Added by Tuan TQ. 29 Dec, 2005. Get WO Detail ID for printing BOM shortage
                    + WOD + Constants.DOT + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + ","
                    //End added

                    //Added by Tuan TQ. 09 Dec, 2005. Add properties
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.SHIFTID_FLD + ","
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD + ","
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.REMARK_FLD + ","
                    + PRO_ShiftTable.TABLE_NAME + Constants.DOT + PRO_ShiftTable.SHIFTDESC_FLD + " as " + PRO_ShiftTable.TABLE_NAME + PRO_ShiftTable.SHIFTDESC_FLD + ","
                    + PRO_IssuePurposeTable.TABLE_NAME + Constants.DOT + PRO_IssuePurposeTable.DESCRIPTION_FLD + " as " + PRO_IssuePurposeTable.TABLE_NAME + PRO_IssuePurposeTable.DESCRIPTION_FLD + ","
                    //End added

                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.QASTATUS_FLD
                    + " FROM " + PRO_WorkOrderCompletionTable.TABLE_NAME + WOC
                    + " INNER JOIN " + PRO_WorkOrderMasterTable.TABLE_NAME + WOM + " ON "
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.WORKORDERMASTERID_FLD + " = " + WOM + Constants.DOT + PRO_WorkOrderMasterTable.WORKORDERMASTERID_FLD
                    + " INNER JOIN " + PRO_WorkOrderDetailTable.TABLE_NAME + WOD + " ON "
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD + " = " + WOD + Constants.DOT + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD
                    + " INNER JOIN " + MST_LocationTable.TABLE_NAME + LOC + " ON "
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.LOCATIONID_FLD + " = " + LOC + Constants.DOT + MST_LocationTable.LOCATIONID_FLD
                    + " LEFT JOIN " + MST_BINTable.TABLE_NAME + BIN + " ON "
                    + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.BINID_FLD + " = " + BIN + Constants.DOT + MST_BINTable.BINID_FLD

                    //Added by Tuan TQ. 09 Dec, 2005
                    + " LEFT JOIN " + PRO_ShiftTable.TABLE_NAME
                    + " ON " + PRO_ShiftTable.TABLE_NAME + Constants.DOT + PRO_ShiftTable.SHIFTID_FLD + "=" + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.SHIFTID_FLD

                    + " LEFT JOIN " + PRO_IssuePurposeTable.TABLE_NAME
                    + " ON " + PRO_IssuePurposeTable.TABLE_NAME + Constants.DOT + PRO_IssuePurposeTable.ISSUEPURPOSEID_FLD + "=" + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.ISSUEPURPOSEID_FLD
                    //End added

                    + " WHERE " + WOC + Constants.DOT + PRO_WorkOrderCompletionTable.WORKORDERCOMPLETIONID_FLD + " = " + pintWOCompletionID;

                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, PRO_WorkOrderCompletionTable.TABLE_NAME);

                return dstPCS.Tables[0].Rows[0];
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }

        }

        /// <summary>
        /// GetWorkingTimeByProductionLineAndYearMonth
        /// </summary>
        /// <param name="pintProductionLineID"></param>
        /// <param name="pintYear"></param>
        /// <param name="pintMonth"></param>
        /// <returns></returns>
        /// <author>Trada</author>
        /// <date>Wednesday, July 19 2006</date>
        public DataSet GetWorkingTimeByProductionLineAndYearMonth(int pintProductionLineID, int pintYear, int pintMonth)
        {
            const string METHOD_NAME = THIS + ".GetWorkingTimeByProductionLineAndYearMonth()";
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = "SELECT "
                    + PCSComUtils.Common.PRO_WorkingTime.WORKINGTIMEID_FLD + ", "
                    + PCSComUtils.Common.PRO_WorkingTime.ENDTIME_FLD + ", "
                    + PCSComUtils.Common.PRO_WorkingTime.STARTTIME_FLD + ", "
                    + PCSComUtils.Common.PRO_WorkingTime.YEARSETUP_FLD + ", "
                    + PCSComUtils.Common.PRO_WorkingTime.MONTHSETUP_FLD + ", "
                    + PCSComUtils.Common.PRO_WorkingTime.PRODUCTIONLINEID_FLD + ", "
                    + PCSComUtils.Common.PRO_WorkingTime.WORKINGHOURS_FLD
                    + " FROM " + PCSComUtils.Common.PRO_WorkingTime.TABLE_NAME
                    + " WHERE " + PCSComUtils.Common.PRO_WorkingTime.PRODUCTIONLINEID_FLD + " = " + pintProductionLineID.ToString()
                    + " AND " + PCSComUtils.Common.PRO_WorkingTime.YEARSETUP_FLD + " = " + pintYear.ToString()
                    + " AND " + PCSComUtils.Common.PRO_WorkingTime.MONTHSETUP_FLD + " = " + pintMonth.ToString();

                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, PCSComUtils.Common.PRO_WorkingTime.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }

        }

        /// <summary>
        /// LoadDataForWOLine
        /// </summary>
        /// <param name="pintWODetailID"></param>
        /// <returns></returns>
        /// <author>Trada</author>
        /// <date>Wednesday, June 22 2005</date>
        public DataSet LoadDataForWOLine(int pintWODetailID)
        {
            const string METHOD_NAME = THIS + ".LoadDataForWOLine()";
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = "SELECT "
                            + PRO + "." + ITM_ProductTable.PRODUCTID_FLD + ", "
                            + PRO + "." + ITM_ProductTable.CODE_FLD + ", "
                            + UM + "." + MST_UnitOfMeasureTable.CODE_FLD + Constants.WHITE_SPACE + CAPTION_UM + ", "
                            + PRO + "." + ITM_ProductTable.DESCRIPTION_FLD + ", "
                            + PRO + "." + ITM_ProductTable.REVISION_FLD + ", "
                            + PRO + "." + ITM_ProductTable.QASTATUS_FLD + ", "
                            + PRO + "." + ITM_ProductTable.LOTCONTROL_FLD + ", "
                            + PRO + "." + ITM_ProductTable.LOTSIZE_FLD + ", "
                            + WOD + "." + PRO_WorkOrderDetailTable.STOCKUMID_FLD + ", "
                            + WOD + "." + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + ", "
                            + " ISNULL(( " + WOD + "." + PRO_WorkOrderDetailTable.ORDERQUANTITY_FLD + " - "
                            + CQ + "." + PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD + " - "
                            + SQ + "." + PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD + "),0) " + OPENQUANTITY
                            + " FROM " + PRO_WorkOrderDetailTable.TABLE_NAME + Constants.WHITE_SPACE + WOD
                            + " INNER JOIN " + ITM_ProductTable.TABLE_NAME + Constants.WHITE_SPACE + PRO
                            + " ON " + WOD + "." + PRO_WorkOrderDetailTable.PRODUCTID_FLD + " = " + PRO + "." + ITM_ProductTable.PRODUCTID_FLD
                            + " INNER JOIN " + MST_UnitOfMeasureTable.TABLE_NAME + Constants.WHITE_SPACE + UM
                            + " ON " + WOD + "." + PRO_WorkOrderDetailTable.STOCKUMID_FLD + " = " + UM + "." + MST_UnitOfMeasureTable.UNITOFMEASUREID_FLD
                            + " LEFT JOIN ("
                            + " SELECT " + PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD + ", "
                            + " SUM(" + PRO_WorkOrderCompletionTable.COMPLETEDQUANTITY_FLD + ") " + COMPQTY
                            + " FROM " + PRO_WorkOrderCompletionTable.TABLE_NAME
                            + " GROUP BY " + PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD
                            + " ) " + CQ + " ON " + WOD + "." + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + " = "
                            + CQ + "." + PRO_WorkOrderCompletionTable.WORKORDERDETAILID_FLD
                            + " LEFT JOIN ( "
                            + " SELECT " + PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD + ", "
                            + " SUM(" + PRO_AOScrapDetailTable.SCRAPQUANTITY_FLD + ") " + SCRAPQUANTITY
                            + " FROM " + PRO_AOScrapDetailTable.TABLE_NAME
                            + " GROUP BY " + PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD
                            + " ) " + SQ + " ON " + WOD + "." + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + " = " + SQ + "." + PRO_AOScrapDetailTable.WORKORDERDETAILID_FLD
                            + " WHERE " + WOD + "." + PRO_WorkOrderDetailTable.WORKORDERDETAILID_FLD + " = " + pintWODetailID;
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, PRO_WorkOrderCompletionTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }

        }
        public DataSet SearchForMultiCompletion(int pintSaleOrderDetailID, int pintProductionLineID, int pintWorkCenterID, int pintLocationID)
        {
            const string METHOD_NAME = THIS + ".SearchForMultiCompletion()";
            DataSet dstPCS = new DataSet();
            OleDbConnection oconPCS = null;
            OleDbCommand ocmdPCS = null;
            try
            {
                string strSql = " SELECT CAST(0 AS bit) " + Constants.SELECTED_STR + ", 1 CCNID,"
                    + " (SELECT BinID FROM MST_BIN WHERE BinTypeID = " + (int)BinTypeEnum.IN
                    + " AND LocationID = " + pintLocationID + ") BinID, "
                    + " D.ProductID, D.StockUMID, D.WorkOrderMasterID, D.WorkOrderDetailID, "
                    + " M.WorkOrderNo, D.Line, P.Code PartNumber, P.Description PartName, P.Revision Model, D.OrderQuantity,"
                    + " D.OrderQuantity - ISNULL(Qty,0) - ISNULL(NGQuantity,0) CompletedQuantity, ISNULL(Qty,0) - ISNULL(NGQuantity,0) Qty,"
                    + " (SELECT Code FROM MST_BIN WHERE BinTypeID = " + (int)BinTypeEnum.IN
                    + " AND LocationID = " + pintLocationID + ") MST_BinCode, "
                    + " D.OrderQuantity - ISNULL(Qty,0) - ISNULL(NGQuantity,0) " + IV_MiscellaneousIssueDetailTable.QUANTITY_FLD + ","
                    + " CASE ISNULL(P.QAStatus," + (int)EnumQA.NOT_REQUIRE_INSPECTION + ") WHEN " + (int)EnumQA.NOT_REQUIRE_INSPECTION + " THEN " + (int)QAStatusEnum.Passed
                    + " ELSE " + (int)QAStatusEnum.Unknow + " END QAStatus"
                    + " FROM PRO_WorkOrderDetail D JOIN PRO_WorkOrderMaster M ON D.WorkOrderMasterID = M.WorkOrderMasterID"
                    + " JOIN PRO_WORouting R ON D.WorkOrderDetailID = R.WorkOrderDetailID"
                    + " JOIN ITM_Product P ON D.ProductID = P.ProductID"
                    + " LEFT JOIN ("
                    + " SELECT WorkOrderDetailID, SUM(ISNULL(CompletedQuantity,0)) Qty, SUM(ISNULL(NGQuantity,0)) NGQuantity"
                    + " FROM PRO_WorkOrderCompletion"
                    + " GROUP BY WorkOrderDetailID"
                    + " ) WOC ON D.WorkOrderDetailID = WOC.WorkOrderDetailID"
                    + " WHERE D.OrderQuantity - ISNULL(Qty,0) - ISNULL(NGQuantity,0) > 0"
                    + " AND D.Status = " + (int)WOLineStatus.Released
                    + " AND D.SaleOrderDetailID = " + pintSaleOrderDetailID
                    + " AND ISNULL(P.ProductTypeID,0) <> " + (int)EnumProductType.SubMaterial
                    //+ " AND P.Revision <> 'CTTC'"
                    + " AND D.ProductionLineID = " + pintProductionLineID
                    + " AND R.WorkCenterID = " + pintWorkCenterID;
                
                oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
                ocmdPCS = new OleDbCommand(strSql, oconPCS);
                ocmdPCS.Connection.Open();

                OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
                odadPCS.Fill(dstPCS, PRO_WorkOrderCompletionTable.TABLE_NAME);

                return dstPCS;
            }
            catch (OleDbException ex)
            {
                throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
            }
            catch (Exception ex)
            {
                throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
            }

            finally
            {
                if (oconPCS != null)
                {
                    if (oconPCS.State != ConnectionState.Closed)
                    {
                        oconPCS.Close();
                    }
                }
            }
        }
    }
}
